﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;
using AcctruePMS.Bll.Common;
using AcctrueWMS.Foundation.Common.Exception;
using AcctrueWMS.Foundation.DAL;
using AcctrueWMS.Foundation.Web.Session;
using System.Data.SqlClient;

namespace AcctruePMS.Bll.Manager
{
    public class ProjectManager
    {
        public DataTable GetParentProject()
        {
            string sSql = "select projectName [key], projectName [value] from project";
            return WMSqlExcuter.ExecuteDataTable(sSql, CommandType.Text);
        }


        public DataTable GetProjectDismemberment()
        {
            string sSql = "select execID [key], execID [value] from projectdismemberment";
            return WMSqlExcuter.ExecuteDataTable(sSql, CommandType.Text);
        }


        public DataTable GetAllProjectType(string userNo)
        {
            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            //string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "(select A.status,A.count as SUM,sys_dictionary_items.dictValue as statusValue from (select status,count(1) as count from project group by status) as A inner join sys_dictionary_items on(A.status=sys_dictionary_items.dictKey) where sys_dictionary_items.dictName='projectStatus' or sys_dictionary_items.dictName='developProjectStatus') union all (select '0' as status,B.count as SUM,'暂无状态' as statusValue from (select status,count(1) as count from project where status is null or status='' group by status ) as B )";
                return WMSqlExcuter.ExecuteDataTable(sql);

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }

                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "(select A.status,A.count as SUM ,sys_dictionary_items.dictValue as statusValue from (select status,count(1) as count from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) group by status ) as A inner join sys_dictionary_items on(A.status=sys_dictionary_items.dictKey) where sys_dictionary_items.dictName='projectStatus' or sys_dictionary_items.dictName='developProjectStatus') union all (select '0' as status,B.count as SUM,'暂无状态' as statusValue from (select status,count(1) as count from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and  (status is null or status='') group by status) as B) ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }

                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "(select A.status,A.count as SUM,sys_dictionary_items.dictValue as statusValue from (select status,count(1) as count from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) group by status ) as A inner join sys_dictionary_items on(A.status=sys_dictionary_items.dictKey) where sys_dictionary_items.dictName='projectStatus' or sys_dictionary_items.dictName='developProjectStatus') union all (select '0' as status,B.count as SUM,'暂无状态' as statusValue from (select status,count(1) as count from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and ( status is null or status='' )   group by status) as B) ";


                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }

                    else
                    {//普通权限

                        string sql5 = "(select A.status,A.count as SUM,sys_dictionary_items.dictValue as statusValue from (select status,count(1) as count from Project where (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) group by status ) as A inner join sys_dictionary_items on(A.status=sys_dictionary_items.dictKey) where sys_dictionary_items.dictName='projectStatus' or sys_dictionary_items.dictName='developProjectStatus') union all (select '0' as status,B.count as SUM,'暂无状态' as statusValue from (select status,count(1) as count from Project where (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and  (status is null or status='') group by status) as B)";

                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }


        }

        public DataTable GetPartProjectType()
        {
            string userNo;
            userNo = System.Web.HttpContext.Current.Session[SessionConst.UserNo].ToString();

            string con = (string)System.Web.HttpContext.Current.Session["projectCond"];
            string sSql = @" SELECT a.departWBS AS depart,a.projectManager,a.status,b.dictValue AS statusValue,a.[COUNT] AS SUM"
                            + " FROM   ( SELECT STATUS,departWBS,projectManager,COUNT(0) AS COUNT FROM   project where createBy='" + userNo + "'  GROUP BY  STATUS,departWBS,projectManager  ) a"
                            + " LEFT JOIN ( SELECT dictValue,dictKey "
                            + " FROM   dbo.sys_dictionary_items WHERE  dictName = 'projectStatus' ) AS b ON  a.status = b.dictKey ";
            return WMSqlExcuter.ExecuteDataTable(sSql, CommandType.Text);
        }

        public void SetProjectListCondition(string con)
        {
            con = DateHelper.UnEscape(con);
            System.Web.HttpContext.Current.Session["projectCond"] = con;
        }


        public DataTable bb()
        {
            string con = (string)System.Web.HttpContext.Current.Session["projectCond"];
            string sSql = @" SELECT a.departWBS AS depart,projectManager,a.status,b.dictValue AS statusValue,a.[COUNT] AS SUM"
                            + " FROM   ( SELECT STATUS,departWBS,projectManager , COUNT(0) AS COUNT FROM   project  GROUP BY  STATUS,departWBS ,projectManager ) a"
                            + " LEFT JOIN ( SELECT dictValue,dictKey "
                            + " FROM   dbo.sys_dictionary_items WHERE  dictName = 'projectStatus' ) AS b ON  a.status = b.dictKey ";
            return WMSqlExcuter.ExecuteDataTable(sSql, CommandType.Text);
        }



        /// <summary>
        /// 根据权限获取对应的项目键对值
        /// </summary>
        /// <param name="userNo"></param>
        /// <returns></returns>
        public DataTable GetProjectByPower(string userNo)
        {

            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            //string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "select projectGUID [key],projectName [value]  from Project  order by createTime desc ";
                return WMSqlExcuter.ExecuteDataTable(sql);

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "select projectGUID [key],projectName [value] from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) order by createTime desc ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }
                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "select projectGUID [key],projectName [value] from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo)  order by createTime desc ";


                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                    else
                    {//普通权限

                        string sql5 = "select projectGUID [key],projectName [value] from Project where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo)  order by createTime desc ";

                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }


        }
        /// <summary>
        /// 两周之内项目状态没有发生变化，非最终状态和新建状态
        /// 根据用户编号和获取项目
        /// </summary>
        /// <param name="userNo"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetProjectByPowerAndCondition(string userNo, string condition)
        {

            if (condition == "1")
            {
                condition = "projectGUID not in(select projectGUID from projectChangeLog where changeTime between dateadd(day,6-datepart(weekday,getdate())-18,getdate()) and getdate()) and createTime <=dateadd(day,6-datepart(weekday,getdate())-18,getdate()) and status in(select dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictValue not in('结束已验收','已取消','项目关闭','客户已验收','新建'))";

            }
            //状态变化项目查询
            else if (condition == "2")
            {
                condition = "projectGUID in(select projectGUID from projectChangeLog where changeColumn='status' group by projectGUID)";

            }
            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            //string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS,(SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where " + condition + " order by createTime ";
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql);
                return dt;

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS,(SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product, (select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and " + condition + " order by createTime desc ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }
                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS,(SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and " + condition + " order by createTime desc ";


                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                    else
                    {//普通权限

                        string sql5 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS,(SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and " + condition + " order by createTime desc ";

                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }

        }

        /// <summary>
        /// 根据权限获取对应的查看项目
        ///权限分类：技术体系一条线，业务体系一条线，行业产品线一条线，每条线的项目归属人都能看到自己负责的项目，归属人的上级部门领导能看到本部门成员负责的项目，行业产品线负责人能看到本行业产品线所有的项目,其中项目的创建人 也能看到自己所创建的项目
        /// </summary>
        /// <returns></returns>
        public DataTable GetProjectByPower(string userNo, string projectType)
        {
            if (string.IsNullOrEmpty(projectType)) { projectType = " like '%%'"; }
            else
            {

                projectType = " = '" + projectType + "'";
            }
            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            //string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, departWBS, execdepartWBS, projectManager, lastSales, sales, contactCode, contractTime, customName, customRank, projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where projectType" + projectType + " order by createTime desc ";
                return WMSqlExcuter.ExecuteDataTable(sql);

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, departWBS, execdepartWBS, projectManager, lastSales, sales, contactCode, contractTime, customName, customRank, projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and projectType" + projectType + " order by createTime desc ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }
                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, departWBS, execdepartWBS, projectManager, lastSales, sales, contactCode, contractTime, customName, customRank, projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and projectType" + projectType + " order by createTime desc ";


                        try
                        {
                            return WMSqlExcuter.ExecuteRealDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                    else
                    {//普通权限

                        string sql5 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, departWBS, execdepartWBS, projectManager, lastSales, sales, contactCode, contractTime, customName, customRank, projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and projectType" + projectType + " order by createTime desc ";

                        try
                        {
                            return WMSqlExcuter.ExecuteRealDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }


        }
        public DataTable GetProjectByPowerAndNoUpdate(string userNo)
        {


            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

           // string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "select p.projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.changeBefore) as changeBefore,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.ChangeAfter) as ChangeAfter,c.changeTime from Project as p left join(SELECT  projectGUID,changeBefore,ChangeAfter,changeColumn,changeTime FROM dbo.projectChangeLog where changeColumn='status' group by projectGUID,changeColumn,changeTime,changeBefore,ChangeAfter) as c on p.projectGUID=c.projectGUID order by changeTime desc";
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql);
                return dt;

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "select p.projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.changeBefore) as changeBefore,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.ChangeAfter) as ChangeAfter,c.changeTime from Project as p left join(SELECT  projectGUID,changeBefore,ChangeAfter,changeColumn,changeTime FROM dbo.projectChangeLog where changeColumn='status' group by projectGUID,changeColumn,changeTime,changeBefore,ChangeAfter) as c on p.projectGUID=c.projectGUID where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) order by changeTime desc ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }
                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "select p.projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.changeBefore) as changeBefore,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.ChangeAfter) as ChangeAfter,c.changeTime from Project as p left join(SELECT  projectGUID,changeBefore,ChangeAfter,changeColumn,changeTime FROM dbo.projectChangeLog where changeColumn='status' group by projectGUID,changeColumn,changeTime,changeBefore,ChangeAfter) as c on p.projectGUID=c.projectGUID where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) order by changeTime desc ";


                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                    else
                    {//普通权限

                        string sql5 = "select p.projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo, (SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode, contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product, status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.changeBefore) as changeBefore,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=c.ChangeAfter) as ChangeAfter,c.changeTime from Project as p left join(SELECT  projectGUID,changeBefore,ChangeAfter,changeColumn,changeTime FROM dbo.projectChangeLog where changeColumn='status' group by projectGUID,changeColumn,changeTime,changeBefore,ChangeAfter) as c on p.projectGUID=c.projectGUID where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) order by changeTime desc ";

                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }

        }
        public DataTable GetProjectByPowerAndNoCheck(string userNo, string projectType)
        {
            //if (string.IsNullOrEmpty(projectType)) { projectType = " like '%%'"; }
            //else
            //{

            //    projectType = " = '" + projectType + "'";
            //}
            SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
            pars[0].Value = userNo;
            string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

            string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

            //string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人
            string sql2 = "select departWBS from userInfo where userNo=@userNo";
            int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
            if (num > 0)
            {//总经理 和副总经理的权限
                string sql = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS,(SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode,contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where status in(select dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictValue not in('结束已验收','已取消','项目关闭','客户已验收')) and dateadd(m,3,IsNull(contractTime,createTime))<getDate() order by createTime ";
                return WMSqlExcuter.ExecuteDataTable(sql);

            }
            else
            {
                DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                if (dt != null && dt.Rows.Count > 0)
                { //属于行业线负责人
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sb.Append("'");
                        sb.Append(dt.Rows[i].ItemArray[0]);
                        sb.Append("'");
                        if (i < dt.Rows.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (sb.Length == 0) { sb.Append("''"); }
                    string sql3 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode,contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and status in(select dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictValue not in('结束已验收','已取消','项目关闭','客户已验收'))and dateadd(m,3,IsNull(contractTime,createTime))<getDate() order by createTime desc ";

                    try
                    {
                        return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                    }
                    catch (SqlException ex)
                    {

                        return new DataTable();
                    }
                }
                else
                {
                    DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                    if (dt1 != null && dt1.Rows.Count > 0)
                    {//属于部门负责人
                        StringBuilder sb1 = new StringBuilder();
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            sb1.Append("'");
                            sb1.Append(dt1.Rows[i].ItemArray[0]);
                            sb1.Append("'");
                            if (i < dt1.Rows.Count - 1)
                            {
                                sb1.Append(",");
                            }
                        }
                        if (sb1.Length == 0) { sb1.Append("''"); }
                        string sql4 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode,contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and status in(select dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictValue not in('结束已验收','已取消','项目关闭','客户已验收'))and dateadd(m,3,IsNull(contractTime,createTime))<getDate() order by createTime desc ";


                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                    else
                    {//普通权限

                        string sql5 = "select projectGUID, createBy, createTime, modifyBy, modifyTime, parentGUID, projectName, costNo,(SELECT  departName FROM departments  WHERE wbsCode=departWBS) as departWBS, execdepartWBS, (SELECT  displayName FROM userInfo WHERE userNo=projectManager) as projectManager, lastSales, sales, contactCode,contractTime, customName, customRank,(select dictValue as [value] from sys_dictionary_items  where [dictKey]=projectType and dictName='projectType') as projectType, product,(select dictValue from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictKey=status) as status, teamCount, isrelation, agentName, estartTime, rstartTime, eEndTime, rEndTIme, researchTime, guideTime, ANALYSE, DESIGN, PROGMCLOSE, ETESTCLOSE, RELEASECLOSE, EXECSTARTTIME, EXECENDTIME, RCHECKOUTTIME, ECHECKOUTTIME, customContact, contactPhone, solveProcess, industryLineCode from Project where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo) and status in(select dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus') and dictValue not in('结束已验收','已取消','项目关闭','客户已验收'))and dateadd(m,3,IsNull(contractTime,createTime))<getDate() order by createTime desc ";

                        try
                        {
                            return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                        }
                        catch (SqlException ex)
                        {

                            return new DataTable();
                        }
                    }
                }

            }

        }
        /// <summary>
        /// 根据项目ID获取项目名称
        /// </summary>
        /// <param name="projectGUID"></param>
        /// <returns></returns>
        public string GetProjectNameByProjectGUID(string projectGUID)
        {
            string sql = "select projectName from project where projectGUID='" + projectGUID + "'";

            try
            {
                return WMSqlExcuter.ExecuteScalar(sql).ToString();
            }
            catch (SqlException ex)
            {

                return "";
            }

        }


        /// <summary>
        /// 根据项目ID获取项目et
        /// </summary>
        /// <param name="projectGUID">项目ID</param>
        /// <returns></returns>
        public DataTable GetProjectByGUID(string projectGUID)
        {

            string sql = "select * from  Project where projectGUID=@projectGUID";
            IDataParameter[] pars = { 
                                    DBFactory.NewParameter("@projectGUID",projectGUID)
                                    };
            return WMSqlExcuter.ExecuteDataTable(sql, pars);
        }

        /// <summary>
        /// 根据项目日志配置文件信息来获取对应列的项目信息
        /// </summary>
        /// <param name="projectGUID"></param>
        /// <returns></returns>
        public DataTable GetProjectByGUIDAndConfig(string projectGUID)
        {
            DataTable recordLogColumn = new ProjectChangeLogManager().GetProjectNeedRecordLogColumn();//获取项目日志配置文件中列配置
            StringBuilder sqlSB = new StringBuilder();//根据配置文件  来拼接 查询列
            sqlSB.Append("select ");
            int count = recordLogColumn.Rows.Count;
            foreach (DataRow row in recordLogColumn.Rows)
            {
                sqlSB.Append(row["columnName"]);
                count--;
                if (count > 0) { sqlSB.Append(","); }
            }

            sqlSB.Append(" from Project where projectGUID=@projectGUID");
            IDataParameter[] pars = { DBFactory.NewParameter("@projectGUID", projectGUID) };
            try
            {
                return WMSqlExcuter.ExecuteDataTable(sqlSB.ToString(), pars);
            }
            catch (SqlException e)
            {

                return new DataTable();
            }
        }
        public DataTable GetProject(string projectName, string departWBS, string projectManager, string status, string contactCode, string customName, string projectType, string contractTime1, string contractTime2)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("where 1=1");
            if (projectName != string.Empty)
            {
                sb.AppendFormat(" and projectName='{0}'", projectName);
            }
            if (departWBS != string.Empty)
            {
                sb.AppendFormat(" and departWBS='{0}'", departWBS);
            }
            if (projectManager != string.Empty)
            {
                sb.AppendFormat(" and projectManager='{0}'", projectManager);
            }
            if (status != string.Empty)
            {
                sb.AppendFormat(" and [status]='{0}'", status);
            }
            if (contactCode != string.Empty)
            {
                sb.AppendFormat(" and contactCode='{0}'", contactCode);
            }
            if (customName != string.Empty)
            {
                sb.AppendFormat(" and customName='{0}'", customName);
            }
            if (projectType != string.Empty)
            {
                sb.AppendFormat(" and projectType='{0}'", projectType);
            }
            if (contractTime1 != string.Empty && contractTime2 != string.Empty)
            {
                sb.AppendFormat(" and contractTime between '{0}' and '{1}' ",contractTime1,contractTime2);
            }
            else
            {

                if (contractTime1 != string.Empty)
                {
                    sb.AppendFormat(" and contractTime>='{0}'", contractTime1);
                }
                if (contractTime2 != string.Empty)
                {
                    sb.AppendFormat("and contractTime<='{0}'",contractTime2);
                }
            }
            string sql = string.Format("select projectName,departName,displayName,d.dictValue as protectStatus,contactCode,customName,e.dictValue as projectType,Convert(varchar(100),contractTime,23)contractTime from (select projectName,departWBS,projectManager,[status],contactCode,customName,projectType,contractTime from Project {0}) as a left join (SELECT departName,wbscode FROM departments) as b on a.departWBS=b.wbsCode left join (SELECT  displayName,userNo FROM userInfo) as c on a.projectManager=c.userNo left join (select dictValue,dictKey from sys_dictionary_items where (dictName='projectStatus' or dictName='developProjectStatus')) as d on a.status=d.dictKey left join (select dictValue,dictKey from sys_dictionary_items where dictName='projectType')as e on a.projectType=e.dictKey", sb.ToString());

            return WMSqlExcuter.ExecuteRealDataTable(sql);
        }

    }
}
